<?php
class Model_tele extends Model {
	//private $aUser;

	function Model_tele() {
		parent::Model();
		$this->aUser = $this->usersession->currentUser();		
	}

	/**
	 * Functie care aduce datele in grid
	 * @param  $aPost
	 * @return array
	 */
	function getContact($aPost) {

		$iStart = $aPost["start"];
		$iLimit = $aPost["limit"];
		$sDir = $aPost["dir"];
		$sSort = $aPost["sort"];

		$client_id = $this->db->escape($aPost["client_id"]);

		if ($this->aUser["level"] == "user") {
			$filtru_base .= " and tele_user = {$this->aUser["user_id"]} ";
			$filtru_ext .= " and tele_user = {$this->aUser["user_id"]} ";
		}

		if (isset($aPost["fname"]) AND $aPost["fname"] != "") {
			$aPost["fname"] = $this->db->escape($aPost["fname"]);
			$filtru_base .= " and persons.fname REGEXP {$aPost["fname"]} ";
			$filtru_ext .= " and client_persons.fname REGEXP {$aPost["fname"]} ";
		}

		if (isset($aPost["lname"]) AND $aPost["lname"] != "") {
			$aPost["lname"] = $this->db->escape($aPost["lname"]);
			$filtru_base .= " and persons.lname REGEXP {$aPost["lname"]} ";
			$filtru_ext .= " and client_persons.lname REGEXP {$aPost["lname"]} ";
		}

		if (isset($aPost["company"]) AND $aPost["company"] != "") {
			$aPost["company"] = $this->db->escape($aPost["company"]);
			$filtru_base .= " and persons.company REGEXP {$aPost["company"]} ";
			$filtru_ext .= " and client_persons.company REGEXP {$aPost["company"]} ";
		}

		if (isset($aPost["city_id"]) AND $aPost["city_id"] != "") {
			$aPost["city_id"] = $this->db->escape($aPost["city_id"]);
			$filtru_base .= " and persons.city_id = {$aPost["city_id"]} ";
			$filtru_ext .= " and client_persons.city_id = {$aPost["city_id"]} ";
		}

		if (isset($aPost["county_id"]) AND $aPost["county_id"] != "") {
			$aPost["county_id"] = $this->db->escape($aPost["county_id"]);
			$filtru_base .= " and persons.county_id = {$aPost["county_id"]} ";
			$filtru_ext .= " and client_persons.county_id = {$aPost["county_id"]} ";
		}

		if (isset($aPost["position_id"]) AND $aPost["position_id"] != "") {
			$aPost["position_id"] = $this->db->escape($aPost["position_id"]);
			$filtru_base .= " and persons.position_id = {$aPost["position_id"]} ";
			$filtru_ext .= " and client_persons.position_id = {$aPost["position_id"]} ";
		}

		if (isset($aPost["position_sub_id"]) AND $aPost["position_sub_id"] != "") {
			$aPost["position_sub_id"] = $this->db->escape($aPost["position_sub_id"]);
			$filtru_base .= " and persons.position_sub_id = {$aPost["position_sub_id"]} ";
			$filtru_ext .= " and client_persons.position_sub_id = {$aPost["position_sub_id"]} ";
		}
		
		if (isset($aPost["ts_name"]) AND $aPost["ts_name"] != "") {
			$aPost["ts_name"] = $this->db->escape($aPost["ts_name"]);
			$filtru_base .= " and ts_name REGEXP {$aPost["ts_name"]} ";
			$filtru_ext .= " and ts_name REGEXP {$aPost["ts_name"]} ";		
		}	
	
		
		if (isset($aPost["tele_data_revenire"]) AND $aPost["tele_data_revenire"] != "") {
			$aPost["ts_name"] = $this->db->escape(substr($aPost["tele_data_revenire"],0,10));
			$filtru_base .= " and DATE(tele_data_revenire) =  '{$aPost["tele_data_revenire"]}' ";
			$filtru_ext .= " and  DATE(tele_data_revenire) = '{$aPost["tele_data_revenire"]}' ";		
		}
		
		if (isset($aPost["phone"]) AND $aPost["phone"] != "") {
			$aPost["phone"] = $this->db->escape($aPost["phone"]);
			$filtru_base .= " and persons.phone REGEXP {$aPost["phone"]} ";
			$filtru_ext .= " and client_persons.phone REGEXP {$aPost["phone"]} ";
		}
		
		if (isset($aPost["mobil"]) AND $aPost["mobil"] != "") {
			$aPost["mobil"] = $this->db->escape($aPost["mobil"]);
			$filtru_base .= " and persons.mobil REGEXP {$aPost["mobil"]} ";
			$filtru_ext .= " and client_persons.mobil REGEXP {$aPost["mobil"]} ";
		}
		
		if (isset($aPost["city_name"]) AND $aPost["city_name"] != "") {
			$aPost["city_name"] = $this->db->escape($aPost["city_name"]);
			$filtru_base .= " and city.name REGEXP {$aPost["city_name"]}";
			$filtru_ext .= "and city.name REGEXP {$aPost["city_name"]}";
		}

		if (isset($aPost["county_name"]) AND $aPost["county_name"] != "") {
			$aPost["county_name"] = $this->db->escape($aPost["county_name"]);
			$filtru_base .= " and county.name REGEXP {$aPost["county_name"]} ";
			$filtru_ext .= "and county.name REGEXP {$aPost["county_name"]} " ;
		}		
		
		
		if (isset($aPost["name"]) AND $aPost["name"] != "") {
			$aPost["name"] = $this->db->escape($aPost["name"]);
			$filtru_base .= " and user.name REGEXP {$aPost["name"]} ";
			$filtru_ext .= " and user.name REGEXP {$aPost["name"]} ";
		}
		
		if (isset($aPost["email"]) AND $aPost["email"] != "") {
			$aPost["email"] = $this->db->escape($aPost["email"]);
			$filtru_base .= " and persons.email REGEXP {$aPost["email"]} ";
			$filtru_ext .= " and client_persons.email REGEXP {$aPost["email"]} ";
		}
		
		if (isset($aPost["email2"]) AND $aPost["email2"] != "") {
			$aPost["email2"] = $this->db->escape($aPost["email2"]);
			$filtru_base .= " and persons.email2 REGEXP {$aPost["email2"]} ";
			$filtru_ext .= " and client_persons.email2 REGEXP {$aPost["email2"]} ";
		}

		


		/*if (isset($aPost["ts_id"]) AND $aPost["ts_id"] != "") {
			$sFilterStatus = " IN ";
			$aPost["ts_id"] = str_replace("=", ",", $aPost["ts_id"]);
			if ($aPost["ts_type"] == 0) {
				$sFilterStatus = " NOT IN";
			}
			$filtru_base .= " and client_persons.ts_id {$sFilterStatus} ( {$aPost["ts_id"]} ) ";
			$filtru_ext .= " and client_persons.ts_id {$sFilterStatus} ( {$aPost["ts_id"]} )";
		}*/

		$select_dbl = "(
        (
    select client_persons_id, persons.lname, `persons`.`fname`,`persons`.`company`,  `client`.`name` as client_name, `client_persons`.`client_id`, `extern` , persons.email, persons.email2, persons.sex,client_persons.unsubscribed, user.name, ts_name, persons.phone, persons.mobil,  DATE_FORMAT(tele_data_revenire, '%d-%b %H:%i') as tele_data_revenire, tele_ultima_modificare, city.name as city_name, county.name as county_name
         from client_persons
          INNER JOIN client on client.client_id=client_persons.client_id AND client_persons.client_id = {$client_id}
          LEFT JOIN persons on persons.person_id=client_persons.person_id
          LEFT JOIN user on user.user_id=client_persons.tele_user
          LEFT JOIN tele_status on client_persons.ts_id=tele_status.ts_id
		  
		   LEFT JOIN city on city.city_id=persons.city_id
		    LEFT JOIN county on county.county_id=persons.county_id
		  
          where extern=0  {$filtru_base} )
    UNION ALL
        ( select client_persons_id,  lname, `fname`,company,`client`.`name` as client_name,client.`client_id`, `extern`,client_persons.email,client_persons.email2, client_persons.sex,unsubscribed, user.name, ts_name, client_persons.phone, client_persons.mobil,  DATE_FORMAT(tele_data_revenire, '%d-%b %H:%i') as tele_data_revenire, tele_ultima_modificare,city.name as city_name, county.name as county_name
             from client_persons
             INNER JOIN client on client.client_id=client_persons.client_id   AND client_persons.client_id = {$client_id}
             LEFT JOIN user on user.user_id=client_persons.tele_user
             LEFT JOIN tele_status on client_persons.ts_id=tele_status.ts_id
			 LEFT JOIN city on city.city_id=client_persons.city_id
		    LEFT JOIN county on county.county_id=client_persons.county_id
             where extern=1 {$filtru_ext} )) as P1 ";

		$query = $this->db->query("select SQL_CALC_FOUND_ROWS * from {$select_dbl}   where 1=1   ORDER BY {$sSort}  {$sDir} LIMIT {$iStart},{$iLimit} ");

		$result = $query->result_array();
		$totalCount = $this->db->query('SELECT FOUND_ROWS() total_rows')->row()->total_rows;

		$data = array(
			'totalCount' => $totalCount,
			'data' => $result
		);
		return $data;
	}

	/**
	 * Transfer contacte la un user
	 * @return void
	 */
	function transferContact($aData) {
		$this->db->set("tele_user", $aData["user_id"]);
		$this->db->where_in("client_persons_id", json_decode($aData["contacts"], true));
		$this->db->update("client_persons");
		return TRUE;
	}

	/**
	 * Functie care aduce datele unui contact de telemarketing
	 * @param  $aData
	 * @return void
	 */
	function getTeleContact($aData) {
		$this->db->select("
                client_persons_id,  extern, persons.person_id,  client_persons.tele_obs, client_persons.ts_id, client_persons.tele_obs_apel_1,
					client_persons.tele_obs_apel_2, client_persons.tele_obs_apel_3,	client_persons.tele_obs_apel_4,		tele_data_revenire, client_persons.unsubscribed, 
				
                IF(extern =1 , client_persons.company , persons.company) as company,
                IF(extern =1 , client_persons.lname , persons.lname) as lname,
                IF(extern =1 , client_persons.fname , persons.fname) as fname,
                IF(extern =0 , persons.address ,  client_persons.address ) as address,
                IF(extern =0 , persons.phone , client_persons.phone) as phone,
                IF(extern =0 , persons.mobil , client_persons.mobil) as mobil,
                IF(extern =0 , persons.web , client_persons.web) as web,
                IF(extern =0 , persons.sex , client_persons.sex) as sex,
                IF(extern =0 , persons.cui , '') as cui,
                IF(extern =0 , D.name , '') as domain_name,
                IF(extern =0 , SD.name , '') as domain_sub_name,
                IF(extern =0 , persons.employees , '') as employees,
                IF(extern =1 , CCo.name , county.name) as county_name,
                IF(extern =1 , CC.name , city.name) as city_name,
                 IF(extern =1 , CP.name , P.name) as position_name,
                 IF(extern =1 , CSP.name , SP.name) as  position_sub_name,


                IF(extern =1 , client_persons.email , persons.email) as email,
                IF(extern =1 , client_persons.email2 , persons.email2) as email2,

                IF(extern =1 , CCo.name , county.name) as county_id_val,
                IF(extern =1 , client_persons.county_id , persons.county_id) as county_id,
                IF(extern =1 , CC.name , city.name) as city_id_val,
                IF(extern =1 , client_persons.city_id , persons.city_id) as city_id,

                IF(extern =1 , CP.name , P.name) as position_id_val,
                IF(extern =1 , client_persons.position_id , persons.position_id) as position_id,
                IF(extern =1 , CSP.name , SP.name) as  position_sub_id_val,
                IF(extern =1 , client_persons.position_sub_id , persons.position_sub_id) as position_sub_id ", false);

		$this->db->join("persons", "persons.person_id=client_persons.person_id and client_persons_id = {$aData["client_persons_id"]}", "inner");

		$this->db->join("domain D", "D.domain_id=persons.domain_id", "left");
		$this->db->join("domain SD", "SD.domain_id=persons.domain_sub_id", "left");

		$this->db->join("position P", "P.position_id=persons.position_id", "left");
		$this->db->join("position SP", "SP.position_id=persons.position_sub_id", "left");
		$this->db->join("city", "city.city_id=persons.city_id", "left");
		$this->db->join("county", "county.county_id=persons.county_id", "left");


		$this->db->join("position CP", "CP.position_id=client_persons.position_id", "left");
		$this->db->join("position CSP", "SP.position_id=client_persons.position_sub_id", "left");
		$this->db->join("city CC", "CC.city_id=client_persons.city_id", "left");
		$this->db->join("county CCo", "CCo.county_id=client_persons.county_id", "left");

		$this->db->join("tele_status", "tele_status.ts_id=client_persons.ts_id", "left");


		return $this->db->get("client_persons")->row_array();
	}

	/**
	 * salvare contact telemarketing
	 * @param  $aData
	 * @return void
	 */
	function saveTeleContact($aData) {

		$aTemp = array(
			"company" => strUpper($aData["company"]),
			"lname" => strTitle($aData["lname"]),
			"fname" => strTitle($aData['fname']),
			"position_id" => $aData["position_id"],
			"position_sub_id" => $aData["position_sub_id"],
			"city_id" => $aData["city_id"],
			"county_id" => $aData["county_id"],
			"phone" => $aData['phone'],
			"mobil" => $aData['mobil'],
			"address" => strTitle($aData['address']),
			"email" => $aData['email'],
			"web" => $aData['web'],
			"sex" => $aData['sex'],
			"email2" => $aData['email2']
		);

		if ($aData["extern"] == "0") {

			$this->db->where('person_id', $aData["person_id"]);
			$result = $this->db->get('persons')->row_array();

			//adaugare date despre ultimele modificari ale userilor daca nu sunt supervisori
			//corector invizibil pentru supervisori
			if ($this->aUser["level"] != "supervisor") {
				$dayRule = date("Y-m-d", strtotime(date("Y-m-d", strtotime($result["date_modified2"])) . "+30 days"));
				if (date("Y-m-d") > $dayRule) {
					$aTemp["modify_by2"] = $result["modify_by"];
					$aTemp["date_modified2"] = $result["date_modified"];
					$aTemp["modify_by"] = $this->aUser["user_id"];
					$aTemp["date_modified"] = date("Y-m-d H:i:s");
				} else {
					$aTemp["modify_by"] = $this->aUser["user_id"];
					$aTemp["date_modified"] = date("Y-m-d H:i:s");
				}
			}

			$this->db->where("person_id", $aData["person_id"]);
			$aResult = $this->db->update("persons", $aTemp);


		}
		if ($aData["extern"] == "1") {
			$this->db->where("client_persons_id", $aData["client_persons_id"]);
			$aResult = $this->db->update("client_persons", $aTemp);
		}
		if ($this->db->affected_rows() > 0) {
			//pentru supervisor nu se logheaza			
			if ($this->aUser["level"] != "supervisor") {
				$this->insertLog($aData["client_persons_id"], "Modificare date contact", "contact_mod", null);
			}
		}

		if (isset($aData["ts_id"]) && $aData["ts_id"] != "") {
			$this->db->where("client_persons_id", $aData["client_persons_id"]);
			$this->db->update("client_persons", array("ts_id" => $aData["ts_id"]));

			$aStatus = $this->db->get_where("tele_status", array("ts_id" => $aData["ts_id"]))->row()->ts_name;	
			//pentru supervisor nu se logheaza
			if ($this->aUser["level"] != "supervisor") {		
				$this->insertLog($aData["client_persons_id"], "Modificare status. Status nou : $aStatus", "status_mod", $aData["ts_id"]);		
			}	
			//supervisorul modifica ultimul status
			else {				
				//aflu care este ultima inregistrare pe status				
				$this->db->where("client_persons_id", $aData["client_persons_id"])	;
				$this->db->where("tl_operation","status_mod");
				$this->db->order_by("tl_id desc");
				$this->db->limit(1);
				$aLogRec = $this->db->get("tele_log")->row_array();		
				
				$this->db->where("tl_id",$aLogRec["tl_id"]);
				$this->db->update("tele_log",array("tl_value"=>$aData["ts_id"],"tl_date"=>$aLogRec["tl_date"]));	
				
			}
		}
		
		//salvare ultima modificare prin telemarketing
		$this->db->where("client_persons_id", $aData["client_persons_id"]);
		$this->db->update("client_persons", array("tele_ultima_modificare"=>date("Y-m-d H:i:s")));

		//salvare observatii
		$this->db->where("client_persons_id", $aData["client_persons_id"]);
		$this->db->update("client_persons", array(
			"tele_obs" => $aData["tele_obs"],
			"tele_obs_apel_1" => $aData["tele_obs_apel_1"],
			"tele_obs_apel_2" => $aData["tele_obs_apel_2"],
			"tele_obs_apel_3" => $aData["tele_obs_apel_3"],
			"tele_obs_apel_4" => $aData["tele_obs_apel_4"],
			"tele_data_revenire" => ($aData["tele_data_revenire"] != "") ? $aData["tele_data_revenire"] : null
		));
		
		if ($this->db->affected_rows() > 0) {
			//pentru supervisor nu se logheaza
			if ($this->aUser["level"] != "supervisor") {
				$this->insertLog($aData["client_persons_id"], "Modificare observatii", "obs_mod", $aData["tele_obs"]);
			}
		}
	}

	/**
	 * Functie care aduce datele in grid
	 */
	function getClientAnswer($aPost) {

		$iClient = $this->db->get_where("client_persons", array("client_persons_id" => $aPost["client_persons_id"]))->row()->client_id;
		$this->db->start_cache();

		$iStart = (isset($aPost["start"]) && ($aPost["start"] != "")) ? $aPost["start"] : 0;
		$iLimit = (isset($aPost["limit"]) && ($aPost["limit"] != "")) ? $aPost["limit"] : 50;
		$sSort = (isset($aPost["sort"]) && ($aPost["sort"] != "")) ? $aPost["sort"] : "tq_name";
		$sDir = (isset($aPost["dir"]) && ($aPost["dir"] != "")) ? $aPost["dir"] : "ASC";

		$this->db->select("tele_question.*, tqa_name, ta_date ");

		$this->db->join("tele_answer", "tele_answer.tq_id=tele_question.tq_id and tele_answer.client_persons_id= {$aPost["client_persons_id"]} ", "left");
		$this->db->join("tele_question_answer", "tele_question_answer.tqa_id=tele_answer.tqa_id ", "left");

		$this->db->where("tq_active", 1);
		$this->db->where("tele_question.client_id", $iClient);
		$this->db->order_by($sSort, $sDir);
		$this->db->limit($iLimit, $iStart);
		$query = $this->db->get("tele_question");

		$totalCount = $this->db->count_all_results("tele_question");
		$aData = $query->result_array();
		$this->db->flush_cache();
		$this->db->stop_cache();

		$data = array(
			'totalCount' => $totalCount,
			'data' => $aData
		);
		return $data;
	}

	/**
	 * Adaugare / editare raspuns
	 * @param  $aData
	 * @return bool|string
	 */
	function saveAnswer($aData) {

		$aEditData = json_decode($aData["data"], TRUE);

		if ($aData["client_persons_id"] == 0) {
			return "Selectati un contact";
		}

		if ($aEditData["tqa_name"] == "") {
			return "Selectati un status";
		}

		$iClient = $this->db->get_where("client_persons", array("client_persons_id" => $aData["client_persons_id"]))->row()->client_id;

		$aDataNew = array(
			"tqa_id" => $aEditData["tqa_name"],
			"tq_id" => $aEditData["tq_id"],
			"client_persons_id" => $aData["client_persons_id"],
			"client_id" => $iClient
		);

		//extragere record actual
		$aDataOld = $this->getAnswer($aData["client_persons_id"], $aEditData["tq_id"]);

		//inserare
		if (count($aDataOld) == 0) {
			$this->db->insert("tele_answer", $aDataNew);
			//$this->insertLog($aData["client_persons_id"], "Adaugare raspuns intrebare
			// {$aEditData["tq_id"]}. Raspuns  : {$aEditData["ta_answer"]}", "answer_insert",
			// $aEditData["tq_id"]);
		}
		//editare
		else {
			$this->db->where("client_persons_id", $aData["client_persons_id"]);
			$this->db->where("tq_id", $aEditData["tq_id"]);
			$this->db->update("tele_answer", $aDataNew);
			//$this->insertLog($aData["client_persons_id"], "Editare raspuns intrebare
			// {$aEditData["tq_id"]}. Raspuns nou  : {$aEditData["ta_answer"]}",
			// "answer_mod", $aEditData["tq_id"]);
		}

		return TRUE;
	}

	/**
	 * Functie care aduce datele despre o inregistrare
	 */
	function getAnswer($iPerson, $iQuestion) {
		$this->db->select("*");
		$this->db->where("client_persons_id", $iPerson);
		$this->db->where("tq_id", $iQuestion);
		return $this->db->get("tele_answer")->row_array();
	}

	/**
	 * Functie care aduce datele in grid
	 */
	function getClientAnswerFree($aPost) {

		$iClient = $this->db->get_where("client_persons", array("client_persons_id" => $aPost["client_persons_id"]))->row()->client_id;
		$this->db->start_cache();

		$iStart = (isset($aPost["start"]) && ($aPost["start"] != "")) ? $aPost["start"] : 0;
		$iLimit = (isset($aPost["limit"]) && ($aPost["limit"] != "")) ? $aPost["limit"] : 50;
		$sSort = (isset($aPost["sort"]) && ($aPost["sort"] != "")) ? $aPost["sort"] : "tqf_name";
		$sDir = (isset($aPost["dir"]) && ($aPost["dir"] != "")) ? $aPost["dir"] : "ASC";

		$this->db->select("tele_questionfree.*, taf_answer, taf_date ");
		$this->db->join("tele_answerfree", "tele_answerfree.tqf_id=tele_questionfree.tqf_id and tele_answerfree.client_persons_id= {$aPost["client_persons_id"]} ", "left");
		$this->db->where("tqf_active", 1);
		$this->db->where("tele_questionfree.client_id", $iClient);
		$this->db->order_by($sSort, $sDir);
		$this->db->limit($iLimit, $iStart);
		$query = $this->db->get("tele_questionfree");

		$totalCount = $this->db->count_all_results("tele_questionfree");
		$aData = $query->result_array();
		$this->db->flush_cache();
		$this->db->stop_cache();

		$data = array(
			'totalCount' => $totalCount,
			'data' => $aData
		);
		return $data;
	}

	/**
	 * Adaugare / editare raspuns
	 * @param  $aData
	 * @return bool|string
	 */
	function saveAnswerFree($aData) {

		$aEditData = json_decode($aData["data"], TRUE);

		if ($aData["client_persons_id"] == 0) {
			return "Selectati un contact";
		}
		$iClient = $this->db->get_where("client_persons", array("client_persons_id" => $aData["client_persons_id"]))->row()->client_id;

		$aDataNew = array(
			"taf_answer" => $aEditData["taf_answer"],
			"tqf_id" => $aEditData["tqf_id"],
			"client_persons_id" => $aData["client_persons_id"],
			"client_id" => $iClient
		);

		//extragere record actual
		$aDataOld = $this->getAnswerFree($aData["client_persons_id"], $aEditData["tqf_id"]);

		//inserare
		if (count($aDataOld) == 0) {
			$this->db->insert("tele_answerfree", $aDataNew);
			//$this->insertLog($aData["client_persons_id"], "Adaugare raspuns intrebare
			// {$aEditData["tq_id"]}. Raspuns  : {$aEditData["ta_answer"]}", "answer_insert",
			// $aEditData["tq_id"]);
		}
		//editare
		else {
			$this->db->where("client_persons_id", $aData["client_persons_id"]);
			$this->db->where("tqf_id", $aEditData["tqf_id"]);
			$this->db->update("tele_answerfree", $aDataNew);
		}

		return TRUE;
	}

	/**
	 * Functie care aduce datele despre o inregistrare
	 */
	function getAnswerFree($iPerson, $iQuestion) {
		$this->db->select("*");
		$this->db->where("client_persons_id", $iPerson);
		$this->db->where("tqf_id", $iQuestion);
		return $this->db->get("tele_answerfree")->row_array();
	}

	/**
	 * Functie care aduce datele in grid
	 */
	function getCustomAnswers($aPost) {

		$iClient = $this->db->get_where("client_persons", array("client_persons_id" => $aPost["client_persons_id"]))->row()->client_id;
		$this->db->start_cache();

		$iStart = (isset($aPost["start"]) && ($aPost["start"] != "")) ? $aPost["start"] : 0;
		$iLimit = (isset($aPost["limit"]) && ($aPost["limit"] != "")) ? $aPost["limit"] : 50;
		$sSort = (isset($aPost["sort"]) && ($aPost["sort"] != "")) ? $aPost["sort"] : "tc_name";
		$sDir = (isset($aPost["dir"]) && ($aPost["dir"] != "")) ? $aPost["dir"] : "ASC";

		$this->db->select("tca_id, tca_answer, tele_custom.tc_id, tele_custom.tc_name, tca_date ");

		$this->db->join("tele_custom_answer", "tele_custom_answer.tc_id=tele_custom.tc_id and tele_custom_answer.client_persons_id= {$aPost["client_persons_id"]} ", "left");
		$this->db->where("tc_active", 1);
		$this->db->where("tele_custom.client_id", $iClient);
		$this->db->order_by($sSort, $sDir);
		$this->db->limit($iLimit, $iStart);
		$query = $this->db->get("tele_custom");

		$totalCount = $this->db->count_all_results("tele_custom");
		$aData = $query->result_array();
		$this->db->flush_cache();
		$this->db->stop_cache();

		$data = array(
			'totalCount' => $totalCount,
			'data' => $aData
		);
		return $data;
	}

	/**
	 * Adaugare / editare raspuns
	 * @param  $aData
	 * @return bool|string
	 */
	function saveCustomAnswer($aData) {

		$aEditData = json_decode($aData["data"], TRUE);

		if ($aData["client_persons_id"] == 0) {
			return "Selectati un contact";
		}
		$iClient = $this->db->get_where("client_persons", array("client_persons_id" => $aData["client_persons_id"]))->row()->client_id;

		$aDataNew = array(
			"tca_answer" => $aEditData["tca_answer"],
			"tc_id" => $aEditData["tc_id"],
			"client_persons_id" => $aData["client_persons_id"],
			"client_id" => $iClient
		);

		//extragere record actual
		$aDataOld = $this->getCustomAnswer($aData["client_persons_id"], $aEditData["tc_id"]);

		//inserare
		if (count($aDataOld) == 0) {
			$this->db->insert("tele_custom_answer", $aDataNew);
		}
		//editare
		else {
			$this->db->where("client_persons_id", $aData["client_persons_id"]);
			$this->db->where("tc_id", $aEditData["tc_id"]);
			$this->db->update("tele_custom_answer", $aDataNew);
		}

		return TRUE;
	}

	/**
	 * Functie care aduce datele despre o inregistrare
	 */
	function getCustomAnswer($iPerson, $iCustom) {
		$this->db->select("*");
		$this->db->where("client_persons_id", $iPerson);
		$this->db->where("tc_id", $iCustom);
		return $this->db->get("tele_custom_answer")->row_array();
	}

	/**
	 * Returnare log
	 * @param  $iContact
	 * @param  $sOperation
	 * @return void
	 */
	function getLog($aPost) {

		if (!isset($aPost["client_persons_id"])) {
			return array();
		}

		$iStart = (isset($aPost["start"]) && ($aPost["start"] != "")) ? $aPost["start"] : 0;
		$iLimit = (isset($aPost["limit"]) && ($aPost["limit"] != "")) ? $aPost["limit"] : 10000;
		$sSort = (isset($aPost["sort"]) && ($aPost["sort"] != "")) ? $aPost["sort"] : "tl_date";
		$sDir = (isset($aPost["dir"]) && ($aPost["dir"] != "")) ? $aPost["dir"] : "desc";

		$this->db->start_cache();
		$this->db->order_by($sSort, $sDir);
		$this->db->limit($iLimit, $iStart);
		$this->db->select("tele_log.*, name");
		$this->db->where("client_persons_id", $aPost["client_persons_id"]);
		$this->db->join("user", "user.user_id=tele_log.user_id", "left");
		$aResult = $this->db->get("tele_log")->result_array();

		$totalCount = $this->db->count_all_results("tele_log");
		$this->db->flush_cache();
		$this->db->stop_cache();
		$data = array(
			'totalCount_log' => $totalCount,
			'data_log' => $aResult
		);
		return $data;
	}

	/**
	 * Returnare log
	 * @param  $iContact
	 * @param  $sOperation
	 * @return void
	 */
	function getLogGeneral($aPost) {

		if (!isset($aPost["client_id"])) {
			return array();
		}

		$iStart = (isset($aPost["start"]) && ($aPost["start"] != "")) ? $aPost["start"] : 0;
		$iLimit = (isset($aPost["limit"]) && ($aPost["limit"] != "")) ? $aPost["limit"] : 10000;
		$sSort = (isset($aPost["sort"]) && ($aPost["sort"] != "")) ? $aPost["sort"] : "tl_date";
		$sDir = (isset($aPost["dir"]) && ($aPost["dir"] != "")) ? $aPost["dir"] : "desc";

		$this->db->select("  SQL_CALC_FOUND_ROWS
                tele_log.*, user.name,
                IF (extern =1 , client_persons.company , persons.company) as company,
                IF (extern =1 , client_persons.lname , persons.lname) as lname,
                IF (extern =1 , client_persons.fname , persons.fname) as fname ", false);

		if ($this->aUser["level"] == "user") {
			$this->db->where("tele_log.user_id", $this->aUser["user_id"]);
		}

		//filter string
		$aFilters = array(
			"lname" => "persons.lname",
			"fname" => "persons.fname",
			"company" => "client_persons.company",
			"name" => "user.name"
		);
		foreach ($aFilters as $key => $aFilter) {
			if (isset($aPost[$key]) && ($aPost[$key] != "")) {
				$this->db->like($aFilter, $aPost[$key]);
			}
		}
		//filter date
		$aFilters = array("tl_date" => "tele_log.tl_date");
		foreach ($aFilters as $key => $aFilter) {
			if (isset($aPost[$key]) && ($aPost[$key] != "")) {
				$this->db->where("DATE({$aFilter})", substr($aPost[$key], 0, 10));
			}
		}

		//quick search
		/* if (isset($aPost["query"]) && ($aPost["query"] != "") &&
		 * isset($aPost["fields"])) {
		 $sQuery = $this->db->escape($aPost["query"]);

		 $this->db->where(" ( persons.lname regexp {$sQuery} or persons.fname regexp
		{$sQuery}
		 or client_persons.lname regexp {$sQuery}  or client_persons.fname regexp
		{$sQuery}
		 or persons.company regexp {$sQuery}  or client_persons.company regexp {$sQuery}
		 or tl_date regexp {$sQuery} or user.name regexp {$sQuery}   )   ", null, false);
		 }*/

		$this->db->order_by($sSort, $sDir);
		$this->db->limit($iLimit, $iStart);
		$this->db->join("client_persons", "client_persons.client_persons_id=tele_log.client_persons_id and client_persons.client_id={$aPost["client_id"]}", "inner");
		$this->db->join("user", "user.user_id=tele_log.user_id", "left");
		$this->db->join("persons", "client_persons.person_id=persons.person_id", "left");
		$this->db->group_by("tele_log.client_persons_id");
		$aResult = $this->db->get("tele_log")->result_array();
		$totalCount = $this->db->query('SELECT FOUND_ROWS() total_rows')->row()->total_rows;
		$data = array(
			'totalCount' => $totalCount,
			'data' => $aResult
		);
		return $data;
	}

	/**
	 * Salvare log
	 * @param  $iContact
	 * @param  $sOperation
	 * @return void
	 */
	function insertLog($iContact, $sOperationText, $sOperation, $sValue = null) {
		$this->db->set("user_id", $this->aUser["user_id"]);
		$this->db->set("client_persons_id", $iContact);
		$this->db->set("tl_operation", $sOperation);
		$this->db->set("tl_value", $sValue);
		$this->db->set("tl_operation_text", $sOperationText);
		$this->db->insert("tele_log");
	}

}
?>